package edu.ynu.se.xiecheng.achitectureclass.project.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Map;
import static java.util.Map.entry;

@Component
public class SoilDataProcessor {

    private static final Map<String, String> TABLE_MAPPING = Map.ofEntries(
            entry("土壤含水率", "soil_moisture"),
            entry("土壤温度", "soil_temperature"),
            entry("土壤电导率", "soil_conductivity"),
            entry("土壤PH值", "soil_ph"),
            entry("土壤氮含量", "soil_nitrogen"),
            entry("土壤磷含量", "soil_phosphorus"),
            entry("土壤钾含量", "soil_potassium"),
            entry("空气温度", "air_temperature"),
            entry("空气相对湿度", "air_humidity"),
            entry("光照度", "light_intensity"),
            entry("二氧化碳", "co2_level")
    );

    private final DataSource dataSource;

    @Autowired
    public SoilDataProcessor(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void process() {
        System.out.println("SoilDataProcessor.process() called.");
        try (Connection conn = dataSource.getConnection()) {
            ensureTablesExist(conn);
            processNewData(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void ensureTablesExist(Connection conn) throws SQLException {
        DatabaseMetaData dbMetaData = conn.getMetaData();
        for (String tableName : TABLE_MAPPING.values()) {
            if (!tableExists(dbMetaData, tableName)) {
                createTable(conn, tableName);
            }
        }
    }

    private boolean tableExists(DatabaseMetaData dbMetaData, String tableName) throws SQLException {
        try (ResultSet rs = dbMetaData.getTables(null, null, tableName, null)) {
            return rs.next();
        }
    }

    private void createTable(Connection conn, String tableName) throws SQLException {
        String sql = "CREATE TABLE " + tableName + " (" +
                "id SERIAL PRIMARY KEY, " +
                "create_time TIMESTAMP, " +
                "value FLOAT, " +
                "device_id INT)";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("Table created: " + tableName);
        }
    }

    private void processNewData(Connection conn) throws SQLException {
        String selectSql = "SELECT * FROM soil_data WHERE create_time > ?";
        String insertSqlTemplate = "INSERT INTO %s (create_time, value, device_id) VALUES (?, ?, ?)";
        String checkSqlTemplate = "SELECT 1 FROM %s WHERE create_time = ?";

        // 获取上次处理的时间
        Timestamp lastProcessedTime = getLastProcessedTime();
        System.out.println("Last processed time: " + lastProcessedTime);

        try (PreparedStatement selectStmt = conn.prepareStatement(selectSql)) {
            selectStmt.setTimestamp(1, lastProcessedTime);
            try (ResultSet rs = selectStmt.executeQuery()) {
                while (rs.next()) {
                    String name = rs.getString("name");
                    Timestamp createTime = rs.getTimestamp("create_time");
                    float value = rs.getFloat("value");
                    int deviceId = rs.getInt("device_id");

                    System.out.println("Processing data: name=" + name + ", createTime=" + createTime + ", value=" + value + ", deviceId=" + deviceId);

                    String tableName = TABLE_MAPPING.get(name);
                    if (tableName != null) {
                        // 检查目标表中是否已存在相同 create_time 的数据
                        String checkSql = String.format(checkSqlTemplate, tableName);
                        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
                            checkStmt.setTimestamp(1, createTime);
                            try (ResultSet checkRs = checkStmt.executeQuery()) {
                                if (!checkRs.next()) { // 如果不存在，则插入新数据
                                    String insertSql = String.format(insertSqlTemplate, tableName);
                                    try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                                        insertStmt.setTimestamp(1, createTime);
                                        insertStmt.setFloat(2, value);
                                        insertStmt.setInt(3, deviceId);
                                        insertStmt.executeUpdate();
                                        System.out.println("Inserted data into table: " + tableName);
                                    }
                                } else {
                                    System.out.println("Data already exists in table: " + tableName);
                                }
                            }
                        }
                    }
                }
            }
        }

        // 更新上次处理的时间
        updateLastProcessedTime(new Timestamp(System.currentTimeMillis()));
    }

    private Timestamp getLastProcessedTime() throws SQLException {
        // 这里可以从数据库或配置文件中读取上次处理的时间
        // 例如：SELECT last_processed_time FROM config_table
        return new Timestamp(0); // 默认返回一个很早的时间
    }

    private void updateLastProcessedTime(Timestamp time) throws SQLException {
        // 这里可以将当前时间存储到数据库或配置文件中
        // 例如：UPDATE config_table SET last_processed_time = ?
    }
}